package com.kehutong.admin.service;

import java.sql.*;
import java.util.List;

import com.kehutong.admin.dao.CorpDatabaseDao;
import com.kehutong.admin.enums.CropPattern;
import com.kehutong.admin.enums.DatabaseTypeEnum;
import com.kehutong.admin.util.RandomUtils;
import org.coraframework.boot.Application;
import org.coraframework.inject.Inject;
import org.coraframework.logger.Logger;
import org.coraframework.logger.LoggerFactory;
import org.coraframework.mvc.http.Bean;
import org.coraframework.mvc.http.ReqMapping;
import org.coraframework.mvc.http.result.Result;
import org.coraframework.mvc.webservice.WebService;
import org.coraframework.orm.Pool;
import org.coraframework.orm.jdbc.JdbcSession;
import org.coraframework.orm.jdbc.connection.DBConnection;
import org.coraframework.orm.jdbc.connection.IDBConfig;
import org.coraframework.orm.jdbc.execute.Condition;
import org.coraframework.threadpool.ThreadPool;

import com.kehutong.admin.entity.Corp;
import com.kehutong.admin.entity.User;
import com.kehutong.admin.entity.db.CorpDatabase;
import com.kehutong.admin.entity.db.Database;
import org.coraframework.util.IDGenerate;
import org.coraframework.util.Objects;

@WebService("/admin/service/auth")
public class AuthService {

	private static final Logger logger = LoggerFactory.getLogger(AuthService.class);

	@Inject
	private JdbcSession jdbcSession;
	@Inject
	private ThreadPool threadPool;
	@Inject
	private CorpDatabaseDao corpDatabaseDao;

	@Inject
	private DBConnection dbConnection;

	@Bean(newInstance = true, copy = true)
	@ReqMapping("/create_corp")
	public Object createCorp(Corp item) throws Exception {
		Corp dbCorp = jdbcSession.findOne(Corp.class).eq("wechatCorpId", item.getWechatCorpId()).exe();
		boolean isAdd = false;
		if (dbCorp == null) {
			isAdd = true;
			//设置第三方授权创建的企业默认为第三方类型
			item.setPattern(CropPattern.THIRD);
			jdbcSession.insert(item);
		}

		if (isAdd) {
			threadPool.execute(()->{
				createDatabase(item);
			});
		}

		return item.getId();
	}

	@Bean(copy = true)
	@ReqMapping("/change_corp")
	public Object changeCorp(Corp item) throws Exception {
		item.setDeleted(false);
		jdbcSession.updateById(item);

		threadPool.execute(()->{
			createDatabase(item);
		});
		return item.getId();
	}

	@ReqMapping("/cancel_corp")
	public Object cancelCorp(Corp item) throws Exception {
		item.setDeleted(true);
		jdbcSession.updateById(item);

		return item.getId();
	}

	@ReqMapping("/get_corp")
	public Object get_corp(Corp item) throws Exception {
		return item;
	}

	@Bean(newInstance = true, copy = true)
	@ReqMapping("/create_user")
	public Object createUser(User item) throws Exception {
		jdbcSession.insert(item);

		return item.getId();
	}

	@Bean(copy = true)
	@ReqMapping("/change_user")
	public Object changeUser(User item) throws Exception {
		item.setDeleted(false);
		jdbcSession.updateById(item);

		return Result.success();
	}

	@Bean(copy = true)
	@ReqMapping("/cancel_user")
	public Object cancelUser(User item) throws Exception {
		item.setDeleted(true);
		jdbcSession.updateById(item);

		return item.getId();
	}

	public void dropDatabase(String id) {
		try {
			// delete from t_corp_database where 1=1 and id='';
			CorpDatabase corpDatabase = jdbcSession.findOne(CorpDatabase.class).eq("id", id).exe();
			jdbcSession.deleteById(CorpDatabase.class, id);

			// delete from sys_corp where 1=1 and id='';
			jdbcSession.deleteById(Corp.class, id);

			Database db = randomDb(corpDatabase.getType());
			String dbName = String.join("", "ww", id);
			Class.forName("com.mysql.cj.jdbc.Driver");
			try (Connection conn = DriverManager.getConnection(db.getUrl(), db.getUsername(), db.getPassword());
				Statement state = conn.createStatement()) {
				state.executeUpdate("DROP DATABASE " + dbName + ";");
			}
		}catch (Exception e){
			logger.error("", e);
		}
	}

	/**
	 * 创建客户通企业数据库
	 * @param corp 企业信息
	 */
	public void createDatabase(Corp corp) {
		Database db = randomDb(DatabaseTypeEnum.product.getCode());
		createDatabase(corp.getId(), corp.getFullName(), db);
	}

	/**
	 * 创建试用数据库池
	 */
	public void createTrialDatabasePool() {
		// 获取试用的数据库连接
		Database db = randomDb(DatabaseTypeEnum.trial.getCode());

		// 获取试用的数据库数量
		List<CorpDatabase> databaseList = jdbcSession.findArray(CorpDatabase.class).eq("type", DatabaseTypeEnum.trial.getCode()).exe();
		int allSize = databaseList.size();
		int inUseSize = 0;
		for (CorpDatabase each : databaseList) {
			if (each.isInUse()) {
				inUseSize++;
			}
		}

		// 创建db
		for (int i = 0; i < db.getCustomerCount() - allSize + inUseSize; i++) {
			createDatabase(IDGenerate.nextId(), DatabaseTypeEnum.trial.getCode() + i, db);
		}
	}

	private void createTrialDatabase(Corp corp) {
		Database db = randomDb(DatabaseTypeEnum.trial.getCode());
		createDatabase(corp.getId(), corp.getFullName(), db);
	}

	private void bindTrialDatabase(Corp corp) {
		List<CorpDatabase> databaseList = jdbcSession.findArray(CorpDatabase.class)
				.eq("type", DatabaseTypeEnum.trial.getCode()).and().eq("inUse", 0).exe();
		if (Objects.isEmpty(databaseList)) {
			createTrialDatabase(corp);
		} else {
			CorpDatabase corpDatabase = databaseList.get(RandomUtils.nextInt(databaseList.size()));
			Condition condition = Condition.createQuery();
			condition.eq("id", corpDatabase.getId());
			Integer integer = corpDatabaseDao.updateToUse(condition);
			if (integer > 0) {
				corpDatabase.setId(corp.getId());
				corpDatabase.setCompanyNo(corp.getId());
				jdbcSession.updateById(corpDatabase);
			} else {
				bindTrialDatabase(corp);
			}
		}
	}

	private void createDatabase(String id, String name, Database db) {
		try {
			String dbName = String.join("", "ww", id);
			Class.forName("com.mysql.cj.jdbc.Driver");
			try (Connection conn = DriverManager.getConnection(db.getUrl(), db.getUsername(), db.getPassword());
				 Statement state = conn.createStatement()) {
				state.executeUpdate("CREATE DATABASE IF NOT EXISTS " + dbName + ";");
			}

			// 查询数据库
			CorpDatabase corpDb = Pool.get(CorpDatabase.class, id);
			boolean isAdd = false;
			if (corpDb == null) {
				isAdd = true;
				corpDb = Pool.newInstance(CorpDatabase.class, id);
			}

			corpDb.setCompanyNo(id);
			corpDb.setCompanyName(name);
			corpDb.setSourceClassName("com.mysql.cj.jdbc.MysqlDataSource");
			corpDb.setUrl(db.getUrl().replace(getDBName(db.getUrl()), dbName));
			corpDb.setUsername(db.getUsername());
			corpDb.setPassword(db.getPassword());
			corpDb.setMinimumSize(db.getMinimumSize());
			corpDb.setMaximumSize(db.getMaximumSize());
			corpDb.setAcquireIncrement(db.getAcquireIncrement());
			corpDb.setAcquireRetryDelay(db.getAcquireRetryDelay());
			corpDb.setLoadFinish(false);
			corpDb.setType(db.getType());
			corpDb.setInUse(db.getType().equals(DatabaseTypeEnum.product.getCode()));
			if (isAdd) {
				jdbcSession.insert(corpDb);
				db.addCustomerCount(1);
			}
			jdbcSession.updateById(db);
			Application.initDatasource(new IDBConfig[]{corpDb});
			//数据库初始化完成，回写完成标志.
			corpDb.setLoadFinish(true);
			jdbcSession.updateById(corpDb);

			// 创建初始化数据
			initCorp(id);
		} catch (Exception e) {
			logger.error("", e);
		}
	}

	private void initCorp(String companyNo) {
		Connection corpConnection = null;
		Connection mainConnection = null;
		PreparedStatement mainStatement = null;
		Statement corpStatement = null;
		ResultSet mainRs = null;
		try {
			corpConnection = dbConnection.getConnection(companyNo);
			mainConnection = dbConnection.getConnection(true);
			mainStatement = mainConnection.prepareStatement("select * from t_corp_init where deleted = 0 order by sort desc");
			mainRs = mainStatement.executeQuery();
			corpStatement = corpConnection.createStatement();
			while (mainRs.next()) {
				String sql = mainRs.getString("sql");
				try {
					int result = corpStatement.executeUpdate(sql);
					logger.info("执行结果result=" + result + ", sql=" + sql);
				}
				catch (Exception exception) {
					logger.error("执行sql时异常", exception);
				}
			}
		} catch (Exception exception) {
			logger.error("初始化时异常", exception);
		} finally {
			close(mainRs, corpStatement, mainStatement, corpConnection, mainConnection);
		}
	}

	private void close(AutoCloseable... autoCloseables) {
		for (AutoCloseable close : autoCloseables) {
			try {
				if (Objects.nonNull(close)) {
					close.close();
				}
			} catch (Exception e) {
				// donothing
			}
		}
	}

	private String getDBName(String url) {
		int index = url.lastIndexOf("?");
		url =  url.substring(0, index);
		index = url.lastIndexOf("/");
		url =  url.substring(index + 1);
		return url;
	}

	private Database randomDb(String type) {
		return jdbcSession.findArray(Database.class)
				.eq("deleted", false).and().eq("type", type)
				.order("customerCount", "ASC")
				.limit(0, 1)
				.exe().get(0);
	}
}
